#!/bin/bash
if [ -n "$2" ] ;then
    do_date=$2
else 
    do_date=`date -d "-1 day" +%F`
fi

sql="

insert overwrite table edu.ads_user_change
select * from edu.ads_user_change
union
select
    churn.dt,
    user_churn_count,
    user_back_count
from
(
    select
        '$do_date' dt,
        count(*) user_churn_count
    from edu.dws_user_user_login_td
    where dt='$do_date'
    and login_date_last=date_add('$do_date',-7)
)churn
join
(
    select
        '$do_date' dt,
        count(*) user_back_count
    from
    (
        select
            user_id,
            login_date_last
        from edu.dws_user_user_login_td
        where dt='$do_date'
    )t1
    join
    (
        select
            user_id,
            login_date_last login_date_previous
        from edu.dws_user_user_login_td
        where dt=date_add('$do_date',-1)
    )t2
    on t1.user_id=t2.user_id
    where datediff(login_date_last,login_date_previous)>=8
)back
on churn.dt=back.dt;

insert overwrite table edu.ads_user_retention
select * from edu.ads_user_retention
union
select
    '$do_date' dt,
    login_date_first create_date,
    datediff('$do_date',login_date_first) retention_day,
    sum(if(login_date_last='$do_date',1,0)) retention_count,
    count(*) new_user_count,
    cast(sum(if(login_date_last='$do_date',1,0))/count(*)*100 as decimal(16,2)) retention_rate
from
(
    select
        user_id,
        date_id login_date_first
    from edu.dwd_user_register_inc
    where dt>=date_add('$do_date',-7)
    and dt<'$do_date'
)t1
join
(
    select
        user_id,
        login_date_last
    from edu.dws_user_user_login_td
    where dt='$do_date'
)t2
on t1.user_id=t2.user_id
group by login_date_first;


insert overwrite table edu.ads_user_stats
select * from edu.ads_user_stats
union
select
    '$do_date' dt,
    t1.recent_days,
    new_user_count,
    active_user_count
from
(
    select
        recent_days,
        sum(if(login_date_last>=date_add('$do_date',-recent_days+1),1,0)) new_user_count
    from edu.dws_user_user_login_td lateral view explode(array(1,7,30)) tmp as recent_days
    where dt='$do_date'
    group by recent_days
)t1
join
(
    select
        recent_days,
        sum(if(date_id>=date_add('$do_date',-recent_days+1),1,0)) active_user_count
    from edu.dwd_user_register_inc lateral view explode(array(1,7,30)) tmp as recent_days
    group by recent_days
)t2
on t1.recent_days=t2.recent_days;

insert overwrite table edu.ads_user_action
select * from edu.ads_user_action
union
select
    '$do_date' dt,
    home.recent_days,
    home_count,
    course_detail_count,
    cart_count,
    order_count,
    payment_count
from
(
    select
        recent_days,
        page_id,
        count(distinct mid_id) home_count 
    from edu.dws_traffic_page_visitor_page_view_1d lateral view explode(array(1,7,30)) tmp as recent_days
    where page_id = 'home'
    and dt >= date_add('$do_date',-recent_days+1)
    group by recent_days,page_id
)home
join
(
    select
        recent_days,
        page_id,
        count(distinct mid_id) course_detail_count 
    from edu.dws_traffic_page_visitor_page_view_1d lateral view explode(array(1,7,30)) tmp as recent_days
    where page_id = 'course_detail'
    and dt >= date_add('$do_date',-recent_days+1)
    group by recent_days,page_id
)course_detail
on course_detail.recent_days=home.recent_days
join
(
    select
        recent_days,
        count(*) cart_count
    from edu.dws_trade_user_cart_add_1d lateral view explode(array(1,7,30)) tmp as recent_days
    where dt >= date_add('$do_date',-recent_days+1)
    group by recent_days
)cart
on home.recent_days=cart.recent_days
join
(
    select
        recent_days,
        count(*) order_count
    from edu.dws_trade_user_order_1d lateral view explode(array(1,7,30)) tmp as recent_days
    where dt >= date_add('$do_date',-recent_days+1)
    group by recent_days
)ord
on cart.recent_days=ord.recent_days
join
(
    select
        recent_days,
        count(*) payment_count
    from edu.dws_trade_user_payment_1d lateral view explode(array(1,7,30)) tmp as recent_days
    where dt >= date_add('$do_date',-recent_days+1)
    group by recent_days
)pay
on cart.recent_days=pay.recent_days;


insert overwrite table edu.ads_new_order_user_stats
select * from edu.ads_new_order_user_stats
union
select 
    '$do_date' dt,
    uo.recent_days,
    new_order_user_count,
    new_payment_user_count
from (
    select
        recent_days,
        sum(if(order_date_first>=date_add('$do_date',-recent_days+1),1,0)) new_order_user_count
    from edu.dws_trade_user_order_td lateral view explode(array(1,7,30)) tmp as recent_days
    where dt='$do_date'
    group by recent_days
)uo
join(
    select
        recent_days,
        sum(if(payment_date_first>=date_add('$do_date',-recent_days+1),1,0)) new_payment_user_count
    from edu.dws_trade_user_payment_td lateral view explode(array(1,7,30)) tmp as recent_days
    where dt='$do_date'
    group by recent_days
)up
on uo.recent_days = up.recent_days;


insert overwrite table edu.ads_age_group_order_user
select 
    '$do_date' dt,
    recent_days,
    concat(substr(birthday,3,1),'0后') age_group,
    count(distinct user_id)
from(
    select 
        recent_days,
        user_id,
        birthday
    from (
        select 
            recent_days,
            user_id
        from edu.dws_trade_user_order_1d lateral view explode(array(1,7,30)) tmp as recent_days
        where dt >= date_add('$do_date',-recent_days+1)
    ) uo
    join edu.dim_user_zip uz
    on uo.user_id = uz.id
)oz
group by recent_days,concat(substr(birthday,3,1),'0后');


insert overwrite table edu.ads_course_review
select
    '$do_date' dt,
    1 recent_days,
    course_id,
    1d_avg_stars as avg_stars,
    user_cnt,
    5_stars_rate
from edu.dws_study_course_avg_stars_1d
where dt = '$do_date'
union
select
    '$do_date' dt,
    recent_days,
    course_id,
    case recent_days
        when 7 then 7d_avg_stars
        when 30 then 30d_avg_stars
    end avg_stars,
    case recent_days
        when 7 then 7d_user_cnt
        when 30 then 30d_user_cnt
    end user_cnt,
    case recent_days
        when 7 then 7d_5_stars_rate
        when 30 then 30d_5_stars_rate
    end 5_stars_rate
from edu.dws_study_course_avg_stars_nd lateral view explode(array(7,30)) tmp as recent_days
where dt = '$do_date';


insert overwrite table edu.ads_study_play_video_by_chapter
select * from edu.ads_study_play_video_by_chapter
union
select
   '$do_date' dt,
   recent_days,
   course_id,
   course_name,
   chapter_id,
   chapter_name,
   video_id,
   video_name,
   video_count,
   video_avg,
   user_count
from
(
select
        1 recent_days,
        course_id,
        course_name,
        chapter_id,
        chapter_name,
        video_id,
        video_name,
        sum(video_count_1d) video_count,        
        avg(video_avg_1d) video_avg,
        sum(user_count_1d) user_count
from edu.dws_study_chapter_process_1d
where dt='$do_date'
group by  course_id,course_name,chapter_id,chapter_name,video_id,video_name
union all
        select
            recent_days,
            course_id,
            course_name,
            chapter_id,
            chapter_name,
            video_id,
            video_name,
            sum(video_count) video_count,        
            avg(video_avg) video_avg,
            sum(user_count) user_count
         from
    (
        select
            recent_days,
            course_id,
            course_name,
            chapter_id,
            chapter_name,
            video_id,
            video_name,
            case recent_days
                when 7 then video_count_7d
                when 30 then video_count_30d
            end video_count,
            case recent_days
                when 7 then video_avg_7d
                when 30 then video_avg_30d
            end video_avg,
            case recent_days
                when 7 then user_count_7d
                when 30 then user_count_30d
            end user_count
        from edu.dws_study_chapter_process_nd lateral view explode(array(7,30)) tmp as recent_days
        where dt='$do_date'
    )t1
    group by course_id,course_name,chapter_id,chapter_name,video_id,video_name,recent_days
)odr;


insert overwrite table edu.ads_study_play_video_by_course
select * from edu.ads_study_play_video_by_course
union
select
   '$do_date' dt,
   recent_days,
   course_id,
   course_name,
   video_count,
   video_avg,
   user_count
from
(
select
        1 recent_days,
        course_id,
        course_name,
        sum(video_count_1d) video_count,        
        avg(video_avg_1d) video_avg,
        sum(user_count_1d) user_count
from edu.dws_study_course_process_1d
where dt='$do_date'
group by  course_id,course_name
union all
        select
            recent_days,
            course_id,
            course_name,
            sum(video_count) video_count,        
            avg(video_avg) video_avg,
            sum(user_count) user_count
         from
    (
        select
            recent_days,
            course_id,
            course_name,
            case recent_days
                when 7 then video_count_7d
                when 30 then video_count_30d
            end video_count,
            case recent_days
                when 7 then video_avg_7d
                when 30 then video_avg_30d
            end video_avg,
            case recent_days
                when 7 then user_count_7d
                when 30 then user_count_30d
            end user_count
        from edu.dws_study_course_process_nd lateral view explode(array(7,30)) tmp as recent_days
        where dt='$do_date'
    )t1
    group by course_id,course_name,recent_days
)odr;



-- 各学科1/7/30日课程交易统计
insert into edu.ads_course_order_by_category
select
'$do_date' dt,
'1' recent_day,
category_id,
category_name,
sum(order_count_1d) order_sum,
count(distinct user_id) people_count,
sum(final_amount_1d) amount_sum
from
edu.dws_trade_user_course_order_1d
where dt='$do_date'
group by category_id,category_name
union all
select
'$do_date' dt,
recent_day,
category_id,
category_name,
sum(order_count) order_sum,
count(distinct (if(order_count>0,user_id,null))) people_count,
sum(final_amount) amount_sum
from(
select
recent_day,
category_id,
category_name,
user_id,
case recent_day
    when 7 then order_count_7d
    when 30 then order_count_30d
end order_count,
case recent_day
    when 7 then final_amount_7d
    when 30 then final_amount_30d
end final_amount
from
edu.dws_trade_user_course_order_nd
lateral view explode(array('7','30')) tmp as recent_day)t1
group by recent_day, category_id, category_name;


-- 各学科1/7/30日课程交易统计
insert into edu.ads_course_order_by_subject
select
'$do_date' dt,
'1' recent_day,
subject_id,
subject_name,
sum(order_count_1d) order_sum,
count(distinct user_id) people_count,
sum(final_amount_1d) amount_sum
from
edu.dws_trade_user_course_order_1d
group by subject_id,subject_name
union all
select
'$do_date' dt,
recent_day,
subject_id,
subject_name,
sum(order_count) order_sum,
count(distinct (if(order_count>0,user_id,null))) people_count,
sum(final_amount) amount_sum
from(
select
recent_day,
subject_id,
subject_name,
user_id,
case recent_day
    when '7' then order_count_7d
    when '30' then order_count_30d
end order_count,
case recent_day
    when '7' then final_amount_7d
    when '30' then final_amount_30d
end final_amount
from
edu.dws_trade_user_course_order_nd
lateral view explode(array('7','30')) tmp as recent_day)t1
group by recent_day, subject_id, subject_name;



-- 各课程1/7/30日课程交易统计
insert into edu.ads_course_order_by_course
select
'$do_date' dt,
'1' recent_day,
course_id,
course_name,
sum(order_count_1d) order_sum,
count(distinct user_id) people_count,
sum(final_amount_1d) amount_sum
from
edu.dws_trade_user_course_order_1d
group by course_id,course_name
union all
select
'$do_date' dt,
recent_day,
course_id,
course_name,
sum(order_count) order_sum,
count(distinct (if(order_count>0,user_id,null))) people_count,
sum(final_amount) amount_sum
from(
select
recent_day,
course_id,
course_name,
user_id,
case recent_day
    when '7' then order_count_7d
    when '30' then final_amount_7d
end order_count,
case recent_day
    when '7' then final_amount_7d
    when '30' then final_amount_30d
end final_amount
from
edu.dws_trade_user_course_order_nd
lateral view explode(array('7','30'))tmp as recent_day)t1
group by recent_day, course_id, course_name;



-- 过去7天各科目课程试听留存统计
insert into edu.ads_course_audition_by_subject
select
'$do_date' recent_day,
subject_id,
subject_name,
datediff('$do_date',first_study)+1 retention_day,
count(*) audition_count,
cast(sum(if(date_id='$do_date',1,0))/count(*)*100  as decimal(16,2)) audition_rate
from(
select
    user_id,
    first_study,
    course_id,
    course_name,
    subject_id,
    subject_name,
    category_id,
    category_name
from
    edu.dws_study_user_audition_nd
where
first_study>date_sub('$do_date',7)
and first_study<='$do_date')audition
left join
(
    select
    user_id, course_id,date_id
    from(
    select
    *
    from
    edu.dwd_trade_order_detail_inc
        where dt='$do_date'
        )t1
    ) orders on audition.user_id=orders.user_id and audition.course_id = orders.course_id
group by first_study,subject_id,subject_name;


-- 过去7天各分类课程试听留存统计
insert into edu.ads_course_audition_by_category
select
'$do_date' recent_day,
category_id,
category_name,
datediff('$do_date',first_study)+1 retention_day,
count(*) audition_count,
cast(sum(if(date_id='$do_date',1,0))/count(*)*100  as decimal(16,2)) audition_rate
from(
select
    user_id,
    first_study,
    course_id,
    course_name,
    subject_id,
    subject_name,
    category_id,
    category_name
from
    edu.dws_study_user_audition_nd
where
first_study>date_sub('$do_date',7)
and first_study<='$do_date')audition
left join
(
    select
    user_id, course_id,date_id
    from(
    select
    *
    from
    edu.dwd_trade_order_detail_inc
        where dt='$do_date'
        )t1
    ) orders on audition.user_id=orders.user_id and audition.course_id = orders.course_id
group by first_study,category_id,category_name;


-- 过去7天各课程课程试听留存统计
insert into edu.ads_course_audition_by_course
select
'$do_date' recent_day,
audition.course_id,
audition.course_name,
datediff('$do_date',first_study)+1 retention_day,
count(*) audition_count,
cast(sum(if(date_id='$do_date',1,0))/count(*)*100  as decimal(16,2)) audition_rate
from(
select
    user_id,
    first_study,
    course_id,
    course_name,
    subject_id,
    subject_name,
    category_id,
    category_name
from
    edu.dws_study_user_audition_nd
where
first_study>date_sub('$do_date',7)
and first_study<='$do_date')audition
left join
(
    select
    user_id, course_id,date_id
    from(
    select
    *
    from
    edu.dwd_trade_order_detail_inc
        where dt='$do_date'
        )t1
    ) orders on audition.user_id=orders.user_id and audition.course_id = orders.course_id
group by first_study,audition.course_id,audition.course_name;


"



case $1 in
    "all" )
        hive -e "$sql"
    ;;
esac